﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Data.Inspection;
using PPI.Entities.Inspections;
using PPI.Entities.Interface;
using PPI.Mobile;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.ServiceLogic.Inspections
{
    public class LInspectionResult :IMobileTable
    {

        private static List<EInspectionResult> GetInspectionResults(string connectionString)
        {
            DataTable dt = DInspectionResult.SelectAll(Int32.MinValue, connectionString).Tables[0];
            return ParseDatatableInspectionResult(connectionString, dt);
        }

        public static DataSet GetInspectionResult(Int32 inspectionResultId,string connectionString)
        {
            return DInspectionResult.SelectAll(inspectionResultId, connectionString);
        }

        public static DataSet GetInspectionResultByInspection(Int32 inspection, string connectionString)
        {
            return DInspectionResult.SelectByInspection(inspection, connectionString);
        }

        private static List<EInspectionResult> ParseDatatableInspectionResult(string connectionString, DataTable dt)
        {
            List<EInspectionResult> _ListResults = new List<EInspectionResult>();

            foreach (DataRow dr in dt.Rows)
            {
                EInspectionResult _result = new EInspectionResult();
                _result.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultId"]);
                _result.Inspection = LInspection.GetById(connectionString, ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructure_PoleFk"]));
                _result.InspectionType = new EInspectionTypes(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_Inspection_TypeFk"]), ConvertHelper.Tostring(dr["Type_Name"]));
                _result.LastInspection = ConvertHelper.Tostring(dr["LastInspection"]);
                _result.Resultype = new EResultType(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultTypeFk"]), ConvertHelper.Tostring(dr["Result_Name"]));
                _result.Notes = LNote.GetByResultId(connectionString, _result.Id);
                _result.IsElectrical = ConvertHelper.Tobool(dr["bElectrical"]);
                _result.Comments = dr["Comments"].ToString();
                _ListResults.Add(_result);

            }
            return _ListResults;
        }

        public static Int32 Save(Int32 inspectionResultId,Int32 InspectionFk, Int32 typeFk, Int32 resultTypeFk, string lastInspection, string comments, string connectionKey, bool isElectrical)
        {
            Int32 resultId = inspectionResultId;
            if (inspectionResultId == Int32.MinValue)
                resultId = Insert(InspectionFk, typeFk, resultTypeFk, lastInspection, comments, isElectrical, connectionKey);
            else
                Update(inspectionResultId, InspectionFk, typeFk, resultTypeFk, lastInspection, comments, isElectrical, connectionKey);
            return resultId;
        }

        private static Int32 Insert(Int32 InspectionFk, Int32 typeFk, Int32 resultTypeFk, string lastInspection, string comments, bool isElectrical, string connectionKey)
        {
            return DInspectionResult.Insert(InspectionFk, typeFk, resultTypeFk, lastInspection, comments,isElectrical, connectionKey);
        }

        private static void Update(Int32 inspectionResultId, Int32 InspectionFk, Int32 typeFk, Int32 resultTypeFk, string lastInspection, string comments, bool isElectrical, string connectionKey)
        {
            DInspectionResult.Update(inspectionResultId, InspectionFk, typeFk, resultTypeFk, lastInspection, comments, isElectrical, connectionKey);
        }

        public List<EMobileQuery> CreateInsertMobileQuery(string connectionKey)
        {
            List<EMobileQuery> listQueries = new List<EMobileQuery>();

            List<EInspection> listInspections = LInspection.SelectAll(connectionKey);
            //List<EInspectionResult> _results = GetInspectionResults(connectionKey);

            StringBuilder _QueryInspections = null;
            EMobileQuery _MobileQueryInspections = new EMobileQuery();
            List<SqlParameter> _QueryParametersInspections;
            
            StringBuilder _QueryResults = null;
            EMobileQuery _MobileQueryResults = new EMobileQuery();
            List<SqlParameter> _QueryParametersResults;

            StringBuilder _QueryImage = null;
            EMobileQuery _MobileQueryImage = new EMobileQuery();
            List<SqlParameter> _QueryParametersImage;

            StringBuilder _QueryDeleteNotes = null;
            EMobileQuery _MobileQueryDeleteNotes = new EMobileQuery("delete tb_UtilityInspectionResultsXNotes where UtilityInspectionResultFk = @UtilityInspectionResultFk ");
            List<SqlParameter> _QueryParametersDeleteNotes;

            StringBuilder _QueryNotes = null;
            EMobileQuery _MobileQueryNotes = new EMobileQuery();
            List<SqlParameter> _QueryParametersNotes;

            #region QueryDeleteInspections
            listQueries.Add(new EMobileQuery("Delete tb_UtilityInspections "));
            #endregion
            
            #region QueryInspection

            _QueryInspections = new StringBuilder();
            _QueryInspections.AppendLine("Insert into tb_UtilityInspections ");
            _QueryInspections.AppendLine(" (");
            _QueryInspections.AppendLine("     UtilityInspectionId,");
            _QueryInspections.AppendLine("     Latitude,");
            _QueryInspections.AppendLine("     Longitude,");
            _QueryInspections.AppendLine("     Year,");
            _QueryInspections.AppendLine("     LVIsit,");
            _QueryInspections.AppendLine("     Number,");
            _QueryInspections.AppendLine("     AcquisitionDate,");
            _QueryInspections.AppendLine("     TreatmentFk,");
            _QueryInspections.AppendLine("     ClassFk,");
            _QueryInspections.AppendLine("     SpecieFk,");
            _QueryInspections.AppendLine("     TopTypeFk,");
            _QueryInspections.AppendLine("     LengthFk,");
            _QueryInspections.AppendLine("     InspectorFk,");
            _QueryInspections.AppendLine("     CompanyFk,");
            _QueryInspections.AppendLine("     MapFk,");
            _QueryInspections.AppendLine("     ProjectFk,");
            _QueryInspections.AppendLine("     ContactFk,");
            _QueryInspections.AppendLine("     Vendor_InspVisit_XMissionStructureFk,");
            _QueryInspections.AppendLine("     SectionFk,");
            _QueryInspections.AppendLine("     GuardsQuantity,");
            _QueryInspections.AppendLine("     PoleTag,");
            _QueryInspections.AppendLine("     Billable,");
            _QueryInspections.AppendLine("     GroundWireRepair,");
            _QueryInspections.AppendLine("     LastCompany,");
            _QueryInspections.AppendLine("     VisitTypeFk");
            _QueryInspections.AppendLine(" )Values(");
            _QueryInspections.AppendLine("     @UtilityInspectionId,");
            _QueryInspections.AppendLine("     @Latitude,");
            _QueryInspections.AppendLine("     @Longitude,");
            _QueryInspections.AppendLine("     @Year,");
            _QueryInspections.AppendLine("     @LVIsit,");
            _QueryInspections.AppendLine("     @Number,");
            _QueryInspections.AppendLine("     @AcquisitionDate,");
            _QueryInspections.AppendLine("     @TreatmentFk,");
            _QueryInspections.AppendLine("     @ClassFk,");
            _QueryInspections.AppendLine("     @SpecieFk,");
            _QueryInspections.AppendLine("     @TopTypeFk,");
            _QueryInspections.AppendLine("     @LengthFk,");
            _QueryInspections.AppendLine("     @InspectorFk,");
            _QueryInspections.AppendLine("     @CompanyFk,");
            _QueryInspections.AppendLine("     @MapFk,");
            _QueryInspections.AppendLine("     @ProjectFk,");
            _QueryInspections.AppendLine("     @ContactFk,");
            _QueryInspections.AppendLine("     @Vendor_InspVisit_XMissionStructureFk,");
            _QueryInspections.AppendLine("     @SectionFk,");
            _QueryInspections.AppendLine("     @GuardsQuantity,");
            _QueryInspections.AppendLine("     @PoleTag,");
            _QueryInspections.AppendLine("     @Billable,");
            _QueryInspections.AppendLine("     @GroundWireRepair,");
            _QueryInspections.AppendLine("     @LastCompany,");
            _QueryInspections.AppendLine("     @VisitTypeFk");
            _QueryInspections.AppendLine(" )");
            _MobileQueryInspections.Query = _QueryInspections.ToString();



            #endregion

            #region QueryResult
            _QueryResults = new StringBuilder();
            _QueryResults.AppendLine("Insert into tb_UtilityInspectionResults ");
            _QueryResults.AppendLine(" (");
            _QueryResults.AppendLine("     UtilityInspectionResultId,");
            _QueryResults.AppendLine("     UtilityInspectionFk,");
            _QueryResults.AppendLine("     InspectionTypeFk,");
            _QueryResults.AppendLine("     ResultTypeFk,");
            _QueryResults.AppendLine("     LastInspection,");
            _QueryResults.AppendLine("     Comments,");
            _QueryResults.AppendLine("     bElectrical");
            _QueryResults.AppendLine(" )Values(");
            _QueryResults.AppendLine("     @UtilityInspectionResultId,");
            _QueryResults.AppendLine("     @UtilityInspectionFk,");
            _QueryResults.AppendLine("     @InspectionTypeFk,");
            _QueryResults.AppendLine("     @ResultTypeFk,");
            _QueryResults.AppendLine("     @LastInspection,");
            _QueryResults.AppendLine("     @Comments,");
            _QueryResults.AppendLine("     @bElectrical");
            _QueryResults.AppendLine(" )");
            _MobileQueryResults.Query = _QueryResults.ToString();



            #endregion

            #region QueryImage

            _QueryImage = new StringBuilder();
            _QueryImage.AppendLine("Insert into tb_Images ");
            _QueryImage.AppendLine(" (");
            _QueryImage.AppendLine("     ImageId,");
            _QueryImage.AppendLine("     Filename,");
            _QueryImage.AppendLine("     UtilityInspectionFk,");
            _QueryImage.AppendLine("     UtilityInspectionResultFk");
            _QueryImage.AppendLine(" ) Values (");
            _QueryImage.AppendLine("     @ImageId,");
            _QueryImage.AppendLine("     @Filename,");
            _QueryImage.AppendLine("     @UtilityInspectionFk,");
            _QueryImage.AppendLine("     @UtilityInspectionResultFk");
            _QueryImage.AppendLine(" )");
            _MobileQueryImage.Query = _QueryImage.ToString();
            
            #endregion

            #region QueryDeleteNotes
            _QueryNotes = new StringBuilder();
            _QueryNotes.AppendLine("Insert into tb_UtilityInspectionResultsXNotes ");
            _QueryNotes.AppendLine(" (");
            _QueryNotes.AppendLine("     UtilityInspectionResultFk,");
            _QueryNotes.AppendLine("     NoteFk");
            _QueryNotes.AppendLine(" )Values(");
            _QueryNotes.AppendLine("     @UtilityInspectionResultFk,");
            _QueryNotes.AppendLine("     @NoteFk");
            _QueryNotes.AppendLine(" )");
            _MobileQueryNotes.Query = _QueryNotes.ToString();

            #endregion

            foreach (EInspection inspection in listInspections)
            {
                _QueryParametersInspections = new List<SqlParameter>();

                #region ParametersInspection

                _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspection.Id));
                _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@TopTypeFk", ParameterDirection.Input, false, inspection.TopType.XMissionStructures_TopTypeId));
                _QueryParametersInspections.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, inspection.Latitude));
                _QueryParametersInspections.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, inspection.Longitude));

                #region year

                if (inspection.Year != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, inspection.Year));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region LastVisit

                if (inspection.LastVisit != DateTime.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateDateTimeParameter("@LVIsit", ParameterDirection.Input, false, inspection.LastVisit));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateDateTimeParameter("@LVIsit", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Number

                if (!String.IsNullOrEmpty(inspection.Number))
                    _QueryParametersInspections.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, inspection.Number));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region AcquisitionDate

                if (inspection.AcquisitionDate != DateTime.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, inspection.AcquisitionDate));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Treatment

                if (inspection.Treatment.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, inspection.Treatment.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Class

                if (inspection.Class.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, inspection.Class.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Specie

                if (inspection.Specie.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, inspection.Specie.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Length

                if (inspection.Length != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, inspection.Length));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Inspector

                if (inspection.Inspector.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, inspection.Inspector.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Company

                if (inspection.Company.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, false, inspection.Company.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Map

                if (inspection.Map.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, false, inspection.Map.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Project

                if (inspection.Project.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, false, inspection.Project.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Contact

                if (inspection.Contact.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ContactFk", ParameterDirection.Input, false, inspection.Contact.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@ContactFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Structure

                if (inspection.Structure.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, false, inspection.Structure.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Section
                if (inspection.Section.Id != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, inspection.Section.Id));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, true, DBNull.Value));

                #endregion

                #region Guards Quantity

                if (inspection.GuardsQuantity != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, inspection.GuardsQuantity));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, DBNull.Value));

                #endregion

                #region Billable

                _QueryParametersInspections.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, inspection.Billable));

                #endregion

                #region GroundWireRepair

                _QueryParametersInspections.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, inspection.GroundWireRepair));

                #endregion

                #region PoleTag

                _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@PoleTag", ParameterDirection.Input, false, inspection.PoleTag));

                #endregion

                #region Last Company

                if (!String.IsNullOrEmpty(inspection.LastCompanyName))
                    _QueryParametersInspections.Add(DBHelper.CreateNVarCharParameter("@LastCompany", 100, ParameterDirection.Input, false, inspection.LastCompanyName));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateNVarCharParameter("@LastCompany", 100, ParameterDirection.Input, true, DBNull.Value));

                #endregion

                #region Vendor Visit Type

                if (inspection.VendorVisitType.Vendor_Visit_TypeId != Int32.MinValue)
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, inspection.VendorVisitType.Vendor_Visit_TypeId));
                else
                    _QueryParametersInspections.Add(DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, true, DBNull.Value));

                #endregion

                #endregion

                _MobileQueryInspections.Parameters.Add(_QueryParametersInspections);


                _QueryParametersResults = new List<SqlParameter>();

                #region ParametersResults
                _QueryParametersResults.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, inspection.Result.Id));
                _QueryParametersResults.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, inspection.Result.Inspection.Id));
                _QueryParametersResults.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, inspection.Result.InspectionType.Id));
                _QueryParametersResults.Add(DBHelper.CreateBitParameter("@bElectrical", ParameterDirection.Input, false, inspection.Result.IsElectrical));
                #endregion

                #region ResultTypeFk

                if (inspection.Result.Resultype.Id != Int32.MinValue)
                    _QueryParametersResults.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, inspection.Result.Resultype.Id));
                else
                    _QueryParametersResults.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region LastInspection

                if (!String.IsNullOrEmpty(inspection.Result.LastInspection))
                    _QueryParametersResults.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, false, inspection.Result.LastInspection));
                else
                    _QueryParametersResults.Add(DBHelper.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, true, DBNull.Value));


                #endregion

                #region Comments

                if (!String.IsNullOrEmpty(inspection.Result.Comments))
                    _QueryParametersResults.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, inspection.Result.Comments));
                else
                    _QueryParametersResults.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, true, DBNull.Value));

                #endregion

                _MobileQueryResults.Parameters.Add(_QueryParametersResults);

                foreach (EInspectionImage i in inspection.Images)
                {
                    _QueryParametersImage = new List<SqlParameter>();

                    #region Paramters
                    _QueryParametersImage.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, i.Id));
                    _QueryParametersImage.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, i.Inspection.Id));
                    _QueryParametersImage.Add(DBHelper.CreateNVarCharParameter("@Filename", 255, ParameterDirection.Input, false, i.FileName));

                    #region UtilityInspectionResultFk

                    if (i.InspectionResult.Id != Int32.MinValue)
                        _QueryParametersImage.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, i.InspectionResult.Id));
                    else
                        _QueryParametersImage.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, true, DBNull.Value));


                    #endregion


                    #endregion

                    _MobileQueryImage.Parameters.Add(_QueryParametersImage);
                }

                _QueryParametersDeleteNotes = new List<SqlParameter>();
                _QueryParametersDeleteNotes.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, inspection.Result.Id));
                _MobileQueryDeleteNotes.Parameters.Add(_QueryParametersDeleteNotes);

                foreach (ENote n in inspection.Result.Notes)
                {

                    _QueryParametersNotes = new List<SqlParameter>();
                    #region Parameters
                    _QueryParametersNotes.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, inspection.Result.Id));
                    _QueryParametersNotes.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, n.Id));


                    #endregion
                    _MobileQueryNotes.Parameters.Add(_QueryParametersNotes);
                }

            }

            listQueries.Add(_MobileQueryInspections);
            listQueries.Add(_MobileQueryResults);
            listQueries.Add(_MobileQueryImage);
            listQueries.Add(_MobileQueryDeleteNotes);
            listQueries.Add(_MobileQueryDeleteNotes);
            listQueries.Add(_MobileQueryNotes);

            return listQueries;
        }

        public static void Delete(Int32 inspectionResultId,string connectionKey)
        {
            DInspectionResult.Delete(inspectionResultId,connectionKey);
        }
    }
}
